/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.mlu.jdbc.simple.support;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.beanutils.BeanUtilsBean2;
import org.mlu.jdbc.simple.utils.JdbcUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * Maps the properties of the bean to the column names of database table according <br/>
 * to the Java Bean naming convention and database table column name naming convention.
 * 
 * @author Mlungisi Sincuba
 * @since 1.0
 */
public class PropertyBeanMapper {
    private static Logger logger = LoggerFactory.getLogger(PropertyBeanMapper.class);
    
    /**
     * 
     * Maps ResultSet to bean according to properties and naming convention.
     * 
     * @param <T> type to convert to
     * @param rs ResultSet data from database
     * @param bean bean to be field with data
     * @param type type to cast data to
     * @return populated bean of type T
     * @throws SQLException in case of any database error
     */
    private <T> T mapRow(ResultSet rs, T bean, Class<T> type) throws SQLException {
        logger.info("Entering mapRow for type: {}", type);
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        BeanUtilsBean2 bean2 = new BeanUtilsBean2();
        try {
            
            if (rs.next()) {
                bean = type.newInstance();
                logger.debug("Got row");
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = JdbcUtils.lookupColumnName(metaData, i);
                    logger.trace("column name is: {}", columnName);
                    String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(columnName);
                    logger.trace("property name is: {}", propertyName);
                    Object value = JdbcUtils.getResultSetValue(rs, i);
                    logger.trace("property value is: {}", value);

                    bean2.setProperty(bean, propertyName, value);
                }
            }
        } catch (InstantiationException ex) {
            logger.error("Could not instantiate Object " + type, ex);
        } catch (IllegalAccessException ex) {
            logger.error("Could not access Object " + type, ex);
        } catch (InvocationTargetException ex) {
            logger.error("Could not invoke Object " + type, ex);
        }
        logger.info("Finished mapRow for object {}", bean);
        return bean;
    }
    
    /**
     * Maps ResultSet to bean according to properties and naming convention and 
     * returns the list of type T.
     * 
     * @param <T> type to convert to
     * @param rs ResultSet data from database
     * @param bean bean to be field with data
     * @param type type to cast data to
     * @return populated bean list of type T
     * @throws SQLException in case of any database error
     */
    public <T> List<T> mapBeanList(ResultSet rs, T bean, Class<T> type) throws SQLException {
        logger.info("Entering mapBeanList");
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        BeanUtilsBean2 bean2 = new BeanUtilsBean2();
        List<T> list = new ArrayList<T>();
        try {
            bean = type.newInstance();
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = JdbcUtils.lookupColumnName(metaData, i);
                    logger.trace("column name is: {}", columnName);
                    String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(columnName);
                    logger.trace("property name is: {}", propertyName);
                    Object value = JdbcUtils.getResultSetValue(rs, i);
                    logger.trace("property value is: {}", value);

                    bean2.setProperty(bean, propertyName, value);
                }
                list.add(bean);
            }
        } catch (InstantiationException ex) {
            logger.error("Could not instantiate Object " + type, ex);
        } catch (IllegalAccessException ex) {
            logger.error("Could not access Object " + type, ex);
        } catch (InvocationTargetException ex) {
            logger.error("Could not invoke Object " + type, ex);
        }
        logger.info("Finished mapBeanList for object list {}", list);
        return list;
    }
    
    /**
     * 
     * @param <T> to cast to
     * @param c connection object for database
     * @param sql to query database
     * @param type to convert to
     * @param params parameters as sql ? replacements
     * @return populated bean list of type T
     */
    public <T> List<T> mapBeanList(Connection c, String sql, Class<T> type, Object ...params) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        T bean = null;
        List<T> list = new ArrayList<T>();
        try {
            ps = c.prepareStatement(sql);
            int i = 1;
            for (Object o : params) {
                ps.setObject(i, o);
                i++;
            }
            rs = ps.executeQuery();
            list = mapBeanList(rs, bean, type);
        } catch (SQLException ex) {
            logger.error("queryForObject with params could not be run", ex);
        } finally {
            JdbcUtils.closeDbRecources(c, ps, rs);
        }
        return list;
    }
    
    /**
     * 
     * @param <T> to cast to
     * @param c connection object for database
     * @param sql to query database
     * @param type to convert to
     * @param params parameters as sql ? replacements
     * @return populated bean
     */
    public <T> T mapRow(Connection c, String sql, Class<T> type, Object ...params) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        T bean = null;
        try {
            ps = c.prepareStatement(sql);
            int i = 1;
            for (Object o : params) {
                ps.setObject(i, o);
                i++;
            }
            rs = ps.executeQuery();
            bean = mapRow(rs, bean, type);
        } catch (SQLException ex) {
            logger.error("queryForObject with params could not be run", ex);
        } finally {
            JdbcUtils.closeDbRecources(c, ps, rs);
        }
        return bean;
    }
    
    /**
     * 
     * @param <T> to cast to
     * @param ds DataSource
     * @param sql to query database
     * @param type to convert to
     * @param params sql arguments
     * @return populated bean
     */
    public <T> T mapRow(DataSource ds, String sql, Class<T> type, Object ...params) {
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        T bean = null;
        try {
            c = ds.getConnection();
            ps = c.prepareStatement(sql);
            logger.debug("running sql: {}", sql);
            int i = 1;
            for (Object o : params) {
                logger.debug("parameter {} set to index {}", new Object[]{o, i});
                ps.setObject(i, o);
                i++;
            }
            rs = ps.executeQuery();
            bean = mapRow(rs, bean, type);
        } catch (SQLException ex) {
            logger.error("queryForObject with params could not be run", ex);
        } finally {
            JdbcUtils.closeDbRecources(c, ps, rs);
        }
        return bean;
    }
}
